/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pos.server.dac.dcs;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.logging.Level;
import java.util.logging.Logger;
import pos.client.dac.dcs.UniversitaService;
import pos.domain.Corso;
import pos.dto.StudenteDTO;
import pos.server.dac.UniConnectionManager;

/**
 *
 * @author jack0niisna
 */
public class UniversitaServiceImpl extends RemoteServiceServlet implements UniversitaService {

    private final String SELECT_SQL = "SELECT * FROM studente WHERE matricola = ?";
    @Override
    public StudenteDTO getStudenteByLogin(String login){
        Connection con = null;
        PreparedStatement pstmt = null;
        StudenteDTO dto = null;
        try{
            con = UniConnectionManager.getConnection();
            pstmt = con.prepareStatement(SELECT_SQL);
            pstmt.setString(1, login);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                dto = new StudenteDTO();
                dto.matricola = login;
                dto.password = rs.getString("password");
                dto.ultima_modifica = rs.getTimestamp("ultima_modifica").getTime();
                dto.is_piano_di_studio_compilato = rs.getBoolean("is_piano_di_studio_compilato");
                dto.id_facolta = rs.getInt("facolta");
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UniversitaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }catch(SQLException ex){
            System.out.println(ex.getMessage());
        }
        finally {
            try {
                if(pstmt != null)
                    pstmt.close();
                if(con != null)
                    con.close();
            } catch (SQLException ex) {
                Logger.getLogger(UniversitaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return dto;
    }
    
    private final String SELECT_CORSI_SQL = "SELECT nome, anno, semestre, crediti, docente FROM corso, esame_da_sostenere WHERE Studente = ? AND Corso_facolta = ? AND nome = Corso_nome AND Facolta = Corso_facolta";
    private final String SELECT_CORSI_SQL2 = "SELECT nome, anno, semestre, crediti, docente FROM corso, esame_sostenuto WHERE Studente = ? AND Corso_facolta = ? AND nome = Corso_nome AND Facolta = Corso_facolta";

    public Collection<Corso> findCorsiByUtente(String login){
        Collection<Corso> res = new HashSet<Corso>();
        Connection con = null;
        PreparedStatement pstmt = null;
        try{
            StudenteDTO dto = getStudenteByLogin(login);
            con = UniConnectionManager.getConnection();
            pstmt = con.prepareStatement(SELECT_CORSI_SQL);
            
            pstmt.setString(1, login);
            pstmt.setInt(2, dto.id_facolta);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                res.add(new Corso(rs.getString("nome"), rs.getInt("anno"), rs.getInt("semestre"), rs.getInt("crediti"), rs.getString("docente")));
            }
            
            pstmt = con.prepareStatement(SELECT_CORSI_SQL2);
            
            pstmt.setString(1, login);
            pstmt.setInt(2, dto.id_facolta);
            rs = pstmt.executeQuery();
            while(rs.next()){
                res.add(new Corso(rs.getString("nome"), rs.getInt("anno"), rs.getInt("semestre"), rs.getInt("crediti"), rs.getString("docente")));
            }
            
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UniversitaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }catch(SQLException ex){
            
        }finally{
            try {
                if(pstmt != null)
                    pstmt.close();
                if(con != null)
                    con.close();
            } catch (SQLException ex) {
                Logger.getLogger(UniversitaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return res;
    }
}
